﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace StudentInfoSystem.Data
{
    class StudentDataAccess
    {
        public static void AddStudent(string fName, string mName, string lName, string faculty, string spec, byte oks, byte status, string facNumber, byte course, string potok, int group)
        {
            var contextStudent = new StudentInfoSystemDataDataContext();
            User newUser = new User();
            newUser.Username = fName + facNumber;
            newUser.Password = facNumber;
            newUser.Role = "Student";
            contextStudent.Users.InsertOnSubmit(newUser);
            contextStudent.SubmitChanges();
            var user = contextStudent.Users
                                 .Where(u => u.Username.Equals(fName + facNumber))
                                 .FirstOrDefault();
            Student newStudent = new Student();
            newStudent.StudentUserID = user.ID;
            newStudent.FirstName = fName;
            newStudent.MiddleName = mName;
            newStudent.LastName = lName;
            newStudent.Faculty = faculty;
            newStudent.Major = spec;
            newStudent.OKS = oks;
            newStudent.StudentStatus = status;
            newStudent.FacultyNumber = facNumber;
            newStudent.Stream = potok;
            newStudent.Group = group;
            newStudent.Course = course;
            contextStudent.Students.InsertOnSubmit(newStudent);
            contextStudent.SubmitChanges();
        }

        public static Student GetStudentInfo(string facultyNumvber)
        {
            var context = new StudentInfoSystemDataDataContext();
            var student = context.Students
                                 .Where(s => s.FacultyNumber.ToUpper() == facultyNumvber.ToUpper())
                                 .FirstOrDefault();

            return student;
        }

        public static Student GetStudentByUserId(int userId)
        {
            var context = new StudentInfoSystemDataDataContext();
            var student = context.Students
                                 .Where(s => s.StudentUserID == userId)
                                 .FirstOrDefault();

            return student;
        }

        public static void SaveStudent(Student student)
        {
            var context = new StudentInfoSystemDataDataContext();
            context.Students.InsertOnSubmit(student);
            context.SubmitChanges();
        }

        public class StudentGroup
        {
            public int Group {get;set;}
        }

        
        public static List<StudentGroup> GetStudentGroups()
        {
            StudentInfoSystemDataDataContext database = new StudentInfoSystemDataDataContext();
            var groups = database.Students.Select(currentStudent => new StudentGroup { Group = currentStudent.Group }).ToList();
            return groups;
        }

        public static IQueryable GetAllStudents()
        {
            StudentInfoSystemDataDataContext database = new StudentInfoSystemDataDataContext();
            var students = database.Students.Select(currentStudent => new { 
                currentStudent.FirstName,
                currentStudent.MiddleName,
                currentStudent.LastName,
                currentStudent.FacultyNumber,
                currentStudent.Faculty,
                currentStudent.Course,
                currentStudent.Group
            });
            return students;
        }

        public static IEnumerable GetStudentsByGroup(int group)
        {
            StudentInfoSystemDataDataContext database = new StudentInfoSystemDataDataContext();
            var students = database.Students.Where(currentStudent => currentStudent.Group == group).Select(currentStudent => new {
                currentStudent.FirstName,
                currentStudent.MiddleName,
                currentStudent.LastName,
                currentStudent.FacultyNumber,
                currentStudent.Faculty,
                currentStudent.Course,
                currentStudent.Group
            });
            return students;
        }

    }
}
